package by.bsu.touragency.db.dao;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import org.apache.log4j.Logger;
import by.bsu.touragency.db.beans.TransportType;
import by.bsu.touragency.db.connection.DBConnector;

public class TransportTypeDAO extends AbstractDAO {
	public static final String SQL_TRANSPORTTYPE_CREATE = "INSERT INTO tourAgencyDB.transportType (name , number)" + "VALUES (?, ?);";
	public static final String SQL_TRANSPORTTYPE_CREATE_GET_ID = "SELECT * FROM tourAgencyDB.transportType " + " WHERE name = ? AND number = ?;";
	public static final String SQL_TRANSPORTTYPE_READ_BY_ID = "SELECT * FROM tourAgencyDB.transportType " + "WHERE (id= ?);";
	public static final String SQL_TRANSPORTTYPE_DELETE = "DELETE FROM tourAgencyDB.transportType WHERE id = ?";
	public static final String SQL_TRANSPORTTYPE_UPDATE = "UPDATE tourAgencyDB.transportType  SET name = ? WHERE (number = ?);";
	private static final String SQL_TRANSPORT_TYPE_IS_EXIST_TYPE_AND_NUMBER = "SELECT COUNT(*) AS rowcount FROM tourAgencyDB.transportType " + "WHERE (name= ?) AND (number=?);";
	private static final String SQL_TRANSPORTTYPE_READ_BY_NAME_AND_NUMBER = "SELECT * FROM tourAgencyDB.transportType " + "WHERE (name= ?) AND (number=?);";

	private static Logger logger = Logger.getLogger("log");

	public boolean isExistByNameAndNumber(TransportType transportType) {
		logger.info("Is meal with this type exist");
		try {// 1
			connector = new DBConnector();
			PreparedStatement statement = connector
					.getPreparedStatement(SQL_TRANSPORT_TYPE_IS_EXIST_TYPE_AND_NUMBER);
			int n;
			try {// 3
				statement.setString(1, transportType.getName());
				statement.setString(2, transportType.getNumber());
				resultSet2 = statement.executeQuery();
				n = -2;
				resultSet2.next();
				n = resultSet2.getInt("rowcount");
			} finally {
			}// 3
			if (resultSet2 != null) {
				resultSet2.close();
			}
			if (statement != null) {
				statement.close();
			}
			if (n > 0) {
				logger.debug("This type already exist");
				return true;
			} else {
				logger.debug("It is new type");
				return false;
			}
		} catch (SQLException e) {
			logger.error("SQL exeption");
			return false;
		} catch (Exception e) {
			logger.error("Undefined exeption");
			return false;
		} finally {
			logger.debug("Close connection");
			close();
		}

	}

	public TransportType create(TransportType transportType) {
		if (!isExistByNameAndNumber(transportType)) {
			logger.info("create transport type");
			try {
				connector = new DBConnector();
				PreparedStatement statement = connector
						.getPreparedStatement(SQL_TRANSPORTTYPE_CREATE);
				statement.setString(1, transportType.getName());
				statement.setString(2, transportType.getNumber());
				statement.executeUpdate();

				statement = connector
						.getPreparedStatement(SQL_TRANSPORTTYPE_CREATE_GET_ID);
				statement.setString(1, transportType.getName());
				statement.setString(2, transportType.getNumber());
				resultSet = statement.executeQuery();

				if (resultSet.next()) {
					transportType.setId(resultSet.getInt("id"));
				}
			} catch (SQLException e) {
				logger.error("SQL exeption");
			} catch (Exception e) {
				logger.error("Undefined exeption");
			} finally {
				logger.debug("Close connection");
				close();
			}
		} else
			transportType = readByNameAndNumber(transportType);
		return transportType;
	}

	public TransportType readById(int id) {
		logger.info("Read transport type by id ");
		TransportType transportType = new TransportType();
		try {
			connector = new DBConnector();
			PreparedStatement statement = connector
					.getPreparedStatement(SQL_TRANSPORTTYPE_READ_BY_ID);
			statement.setInt(1, id);
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				transportType.setId(resultSet.getInt("id"));
				transportType.setName(resultSet.getString("name"));
				transportType.setNumber(resultSet.getString("number"));

			}
			if (statement != null) {
				statement.close();
			}
		} catch (SQLException e) {
			logger.error("SQL exeption");
		} catch (Exception e) {
			logger.error("Undefined exeption");
		} finally {
			logger.debug("Close connection");
			close();
		}

		return transportType;
	}

	public TransportType readByNameAndNumber(TransportType transportType) {
		logger.info("Read transport type by id ");
		try {
			connector = new DBConnector();
			PreparedStatement statement = connector
					.getPreparedStatement(SQL_TRANSPORTTYPE_READ_BY_NAME_AND_NUMBER);
			statement.setString(1, transportType.getName());
			statement.setString(2, transportType.getNumber());
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				transportType.setId(resultSet.getInt("id"));
				transportType.setName(resultSet.getString("name"));
				transportType.setNumber(resultSet.getString("number"));

			}
			if (statement != null) {
				statement.close();
			}
		} catch (SQLException e) {
			logger.error("SQL exeption");
		} catch (Exception e) {
			logger.error("Undefined exeption");
		} finally {
			logger.debug("Close connection");
			close();
		}

		return transportType;
	}

	public void update(TransportType transportType) {
		logger.info("Update transport type");
		try {
			connector = new DBConnector();
			PreparedStatement statement = connector
					.getPreparedStatement(SQL_TRANSPORTTYPE_UPDATE);
			statement.setString(1, transportType.getName());
			statement.setString(2, transportType.getNumber());
			statement.executeUpdate();
			if (statement != null) {
				statement.close();
			}
		} catch (SQLException e) {
			logger.error("SQL exeption");
		} catch (Exception e) {
			logger.error("Undefined exeption");
		} finally {
			logger.debug("Close connection");
			close();
		}
	}

	public void deleteById(int id) {
		logger.info("Delete transport type by id");
		try {
			connector = new DBConnector();
			PreparedStatement statement = connector
					.getPreparedStatement(SQL_TRANSPORTTYPE_DELETE);
			statement.setInt(1, id);
			statement.executeUpdate();
			if (statement != null) {
				statement.close();
			}
		} catch (SQLException e) {
			logger.error("SQL exeption");
		} catch (Exception e) {
			logger.error("Undefined exeption");
		} finally {
			logger.debug("Close connection");
			close();
		}
	}

}
